Calculate the average of numbers

Click a cell below or to the right of the numbers for which you want to find the average.
Click the arrow next to AutoSum on the Standard toolbar, and then click Average, and then press ENTER.

Use the AVERAGE function to do this task.

Worksheet example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic. Do not select the row or column headers.

Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

1

2
3
4
5
6
7

A
Data
10
7
9
27
0
4
Formula	Description (Result)
=AVERAGE(A2:A7)	Averages all of numbers in list above (9.5)
=AVERAGE(A2:A4,A7)	Averages the top three and the last number in the list (7.5)
=AVERAGE(IF(A2:A7<>0, A2:A7,""))	Averages the numbers in the list except those that contain zero, such as cell A6 (11.4)

Note The last formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the cell A11. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the error #VALUE! is returned.

Function details

AVERAGE

Use the SUMPRODUCT and SUM functions to do this task.

Worksheet example

The example may be easier to understand if you copy it to a blank worksheet.

Show How?

Create a blank workbook or worksheet.
Select the example in the Help topic. Do not select the row or column headers.

Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

This example calculates the average price paid for a unit across three purchases, where each purchase is for a different number of units at a different price per unit.

1

2
3
4

A	B
Price per unit	Number of units
20	500
25	750
35	200
Formula	Description (Result)
=SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4)	Divides the total cost of all three orders by the total number of units ordered (24.66)

Function details

SUM

SUMPRODUCT

Use the AVERAGE and IF functions to do this task.

Worksheet example

The example may be easier to understand if you copy it to a blank worksheet.

Show How?

Create a blank workbook or worksheet.
Select the example in the Help topic. Do not select the row or column headers.

Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

1

2
3
4
5
6
7

A
Data
10
7
9
27
0
4
Formula	Description (Result)
=AVERAGE(IF(A2:A7<>0, A2:A7,""))	Averages the numbers in the list except those that contain zero, such as cell A6 (11.4)

Note The formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the cell A9. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the error #VALUE! is returned.

Function details

AVERAGE

IF